/* uedb_sqlite3.c */
#include <windows.h>
#include <io.h>
#include <share.h>
#include <sys/stat.h>
#include <fcntl.h>

/* UELogger Support */
#include "litelog.h"
extern int logfd;

#include "uedb.h"
#include "uedb_sqlite3.h"

int uedb_sqlite3_connect(const char *folder, const char *dbname, sqlite3_connection *pconnection)
{
	char buf[MAX_PATH + 1];
	char filename[5];
	int  len, i, max, fd;
	int  ret = UE_DB_ERR_SUCCESS;

	dbglogex("Enter uedb_sqlite3_connect(%s, %s, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, folder, dbname, pconnection);

	/* parameter check */
	if (pconnection)
	{
		memset(pconnection->folder, 0, MAX_PATH + 1);
		memset(pconnection->dbname, 0, MAX_DB_NAME_LEN + 1);
		pconnection->pdb = 0;
		pconnection->result = NULL;
		pconnection->rows = 0;
		pconnection->columns = 0;

		/* copy the folder path to temp buffer */
		memset(buf, 0, MAX_PATH + 1);
		if (folder)
			strcpy_s(buf, MAX_PATH, folder);

		/* append the final '/' */
		len = strlen(buf);
		if (len > 0)
		{
			if (buf[len - 1] != '/' && buf[len - 1] != '\\')
			{
				if (len < (MAX_PATH - 1))
				{
					strcat_s(buf, MAX_PATH + 1, "/");
					len++;
				}
				else
				{
					dbglogex("The input folder path: %s is too long", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, folder);
					ret = UE_DB_ERR_INVALID_PARAMETER;
				}
			}
		}

		/* normalize folder path success */
		if (ret == UE_DB_ERR_SUCCESS)
		{
			dbglogex("The input folder is: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, buf);
			strcpy_s(pconnection->folder, MAX_PATH + 1, buf);

			if (dbname && strlen(dbname) > 0)
			{
				if ((len + strlen(dbname)) <= MAX_PATH)
				{
					if (strlen(dbname) <= MAX_DB_NAME_LEN)
					{
						strcpy_s(pconnection->dbname, MAX_DB_NAME_LEN + 1, dbname);

						strcat_s(buf, MAX_PATH + 1, dbname);
						dbglogex("Try to open the file: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, buf);

						ret = sqlite3_open(buf, &pconnection->pdb);
						if(ret != SQLITE_OK)
						{
							dbglogex("Open database %s failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, buf, ret);
							ret = UE_DB_ERR_FILE_IO;
						} /* open specified database failed */
						else
						{
							dbglogex("Open database %s success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, buf);
							ret = UE_DB_ERR_SUCCESS;
						} /* open specified database success */
					}
					else
					{
						dbglogex("The length of DB name: %s is too long", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, dbname);
						ret = UE_DB_ERR_INVALID_PARAMETER;
					} /* DB name length is too long */	
				} /* full path length valid */
				else
				{
					dbglogex("The full file path with folder: %s and file name: %s is too long", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, folder, dbname);
					ret = UE_DB_ERR_INVALID_PARAMETER;
				} /* full path length invalid */
			} /* dbname specified */
			else
			{
				dbglog("DB name not specified, check if folder has enough priviledge", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

				max = __min((MAX_PATH - len), 100) * 10;
				for (i = 0; i < max; i++)
				{
					_itoa_s(i, filename, 5, 10);
					strcat_s(buf, MAX_PATH + 1, filename);
					if (_access(buf, 0) == -1)
						break;
					buf[len] = 0;
				}

				if (i < max)
				{
					dbglogex("Check if we can create new file: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, buf);

					fd = 0;
					ret = _sopen_s(&fd, buf, _O_CREAT | _O_RDWR, _SH_DENYRW, _S_IREAD | _S_IWRITE);
					if (!ret && fd > 0)
					{
						dbglog("Check if we can write data to the new created file", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
						len = _write(fd, buf, strlen(buf));
						if (len == (int)strlen(buf))
						{
							_close(fd);

							if (remove(buf) == 0)
							{
								dbglog("Remove the temp file success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
							} /* remove file success */
							else
							{
								dbglog("Remove the temp file failed", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
								ret = UE_DB_ERR_FILE_IO;
							} /* remove file failed */
						} /* write data to file success */
						else
						{
							dbglog("Write data to file failed", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
							ret = UE_DB_ERR_FILE_IO;

							_close(fd);
							remove(buf);
						} /* write data to file failed */
					} /* create new file success */
					else
					{
						dbglogex("Create new file: %s failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, buf, ret);
						ret = UE_DB_ERR_FILE_IO;
					} /* create new file failed */
				} /* find temp test file name success */
				else
				{
					dbglog("Too many files in specified folders", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
					ret = UE_DB_ERR_INVALID_PARAMETER;
				} /* find temp test file name failed for too many files in specified folders */
			} /* No specify dbname */
		} /* normalize input folder path success */
	} /* parameter check success */
	else
	{
		dbglog("Input connection is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_connect", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_create(const char *dbspec, sqlite3_connection *pconnection)
{
	int  ret = UE_DB_ERR_SUCCESS;
	const char *next = dbspec;
	char *error = NULL;

	dbglogex("Enter uedb_sqlite3_create(%s, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbspec, pconnection);

	/* parameter check */
	if (pconnection && pconnection->pdb && dbspec)
	{
		dbglog("Loop to execute each SQL", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

		/* loop to execute each sql */
		while (strlen(next))
		{
			dbglogex("Execute SQL: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, next);

			ret = sqlite3_exec(pconnection->pdb, next, 0,0, &error);
			if(ret != SQLITE_OK)
			{
				dbglogex("Execute SQL command: %s failed, Error Description: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, next, error);
				sqlite3_free(error);
				ret = UE_DB_ERR_SQL_EXECUTION;
			}
			else
			{
				ret = UE_DB_ERR_SUCCESS;
			}

			next = next + strlen(next) + 1;
		} /* enc of loop to execute each sql */
	} /* parameter check success */
	else
	{
		dbglog("No valid database connection or dbspec", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_create", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_close(sqlite3_connection *pconnection)
{
	int  ret = UE_DB_ERR_SUCCESS;

	dbglogex("Enter uedb_sqlite3_close(%x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, pconnection);

	if (pconnection)
	{
		/* close query on this connection if any */
		uedb_sqlite3_close_query(pconnection);

		if (pconnection->pdb)
			sqlite3_close(pconnection->pdb);

		free(pconnection);
	} /* parameter check failed */
	else
	{
		dbglog("Input connection is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_close", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_execute(const char *sql, sqlite3_connection *pconnection)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;

	dbglogex("Enter uedb_sqlite3_execute(%s, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, sql, pconnection);

	if (pconnection)
	{
		ret = sqlite3_exec(pconnection->pdb, sql, 0,0, &error);
		if(ret != SQLITE_OK)
		{
			dbglogex("Execute SQL command: %s failed, Error Description: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, sql, error);
			sqlite3_free(error);
			ret = UE_DB_ERR_SQL_EXECUTION;
		}
		else
		{
			ret = UE_DB_ERR_SUCCESS;
		}
	} /* parameter check failed */
	else
	{
		dbglog("Input connection is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_execute", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_execute_ex(const char *sql, const uedb_parameter *params, const unsigned int param_num, sqlite3_connection *pconnection)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;
	sqlite3_stmt*   stmt = NULL;
	unsigned int i;

	dbglogex("Enter uedb_sqlite3_execute_ex(%s, %x, %x, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, sql, params, param_num, pconnection);

	/* parameter check */
	if (pconnection && sql && strlen(sql) > 0 && (param_num == 0 || param_num > 0 && params))
	{
		/* prepare sql statement */
		ret = sqlite3_prepare_v2(pconnection->pdb, sql, -1, &stmt, NULL);
		if(ret == SQLITE_OK)
		{
			dbglog("Prepare SQL command success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

			/* loop to bind all the parameters */
			for (i = 0; i < param_num; i++)
			{
				switch(params[i].type)
				{
				/* binary */
				case UE_DB_PARAM_TYPE_BINARY:
					dbglogex("The %d parameter is binary", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, i);
					ret = sqlite3_bind_blob(stmt, i + 1, params[i].data, params[i].len, NULL);
					break;
				/* unknown */
				default:
					dbglogex("Unkown type of %d parameter: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, i, params[i].type);
					ret = UE_DB_ERR_SQL_EXECUTION;
					break;
				}

				if (!ret)
				{
					dbglogex("Bind the %d parameter success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, i);
				}
				else
				{
					dbglogex("Bind the %d parameter failed with return value: %d", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, ret);
					ret = UE_DB_ERR_SQL_EXECUTION;
					break;
				}
			} /* finish loop to bind parameters */

			/* execute the sql statement */
			if (!ret)
			{
				ret = sqlite3_step(stmt);
				if (ret == SQLITE_DONE)
				{
					dbglog("Execute the SQL statement success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
					ret = UE_DB_ERR_SUCCESS;
				}
				else
				{
					dbglogex("Execute the SQL statement failed with return value", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
					ret = UE_DB_ERR_SQL_EXECUTION;
				}
			} /* execute the sql statement */

			sqlite3_finalize(stmt);
		} /* prepare sql command success */
		else
		{
			dbglogex("Prepare SQL command: %s failed, Error Code: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, sql, ret);
			ret = UE_DB_ERR_SQL_EXECUTION;
		} /* prepare sql command failed */
	} /* parameter check failed */
	else
	{
		dbglog("Input connection is null or parameter is invalid", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_execute", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_query(const char *sql, sqlite3_connection *pconnection)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;

	dbglogex("Enter uedb_sqlite3_query(%s, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, sql, pconnection);

	if (pconnection)
	{
		ret = sqlite3_get_table(pconnection->pdb, sql, &(pconnection->result),&(pconnection->rows), &(pconnection->columns), &error);
		if(ret != SQLITE_OK)
		{
			dbglogex("Execute SQL command: %s failed, Error Description: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, sql, error);
			sqlite3_free_table(pconnection->result);
			pconnection->result = NULL;
			pconnection->rows = 0;
			pconnection->columns = 0;
			ret = UE_DB_ERR_SQL_EXECUTION;
		}
		else
		{
			ret = UE_DB_ERR_SUCCESS;
		}
	} /* parameter check failed */
	else
	{
		dbglog("Input connection is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_query", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_close_query(sqlite3_connection *pconnection)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;

	dbglogex("Enter uedb_sqlite3_close_query(%x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, pconnection);

	if (pconnection)
	{
		if (pconnection->result)
		{
			dbglogex("Close query result %x", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, pconnection->result);
			sqlite3_free_table(pconnection->result);
			pconnection->result = NULL;
			pconnection->rows = 0;
			pconnection->columns = 0;
		}
		else
		{
			dbglog("No result to close", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
		}
	} /* parameter check failed */
	else
	{
		dbglog("Input connection is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_close_query", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_rows(sqlite3_connection *pconnection, int *prows)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;

	dbglogex("Enter uedb_sqlite3_rows(%x, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, pconnection, prows);

	if (pconnection && prows)
	{
		*prows = 0;

		if (pconnection->result)
		{
			*prows = pconnection->rows;
		}
	} /* parameter check failed */
	else
	{
		dbglog("Input connection or prows is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_rows", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_result_by_name(const char *column_name, const int row, sqlite3_connection *pconnection, const char **presult)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;
	int i;

	dbglogex("Enter uedb_sqlite3_result(%s, %d, %x, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, column_name, row, pconnection, presult);

	if (column_name && strlen(column_name) > 0 && row >= 0 && pconnection && presult)
	{
		*presult = 0;

		/* check last query result */
		dbglogex("Last query result %x and row number %d", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, pconnection->result, pconnection->rows);
		if (pconnection->result && row < pconnection->rows)
		{
			/* try to find the specified column */
			ret = UE_DB_ERR_UNKNOWN_COLUMN;
			for (i = 0; i < pconnection->columns; i++)
			{
				if (!strcmp(column_name, pconnection->result[i]))
				{
					ret = UE_DB_ERR_SUCCESS;
					dbglogex("Column found, column index is %d", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, i);
					break;
				}
			}

			if (ret == UE_DB_ERR_SUCCESS)
			{
				i = pconnection->columns * (row + 1) + i;
				dbglogex("Index of the result %d", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, i);

				*presult = pconnection->result[i];
			} /* column found */
			else
			{
				dbglog("Column not found", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			} /* column not found */
		} /* check last query result success */
		else
		{
			dbglog("No last query result or index number is too big", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			ret = UE_DB_ERR_INVALID_PARAMETER;
		} /* check last query result failed */
	} /* parameter check failed */
	else
	{
		dbglog("Input parameter is invalid", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_result", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}

int uedb_sqlite3_result_by_index(const int column, const int row, sqlite3_connection *pconnection, const char **presult)
{
	int  ret = UE_DB_ERR_SUCCESS;
	char *error = NULL;
	int index;

	dbglogex("Enter uedb_sqlite3_result(%d, %d, %x, %x)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, column, row, pconnection, presult);

	if (column >= 0 && row >= 0 && pconnection && presult)
	{
		*presult = 0;

		/* check last query result */
		dbglogex("Last query result %x and row number %d and column number %d", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, pconnection->result, pconnection->rows, pconnection->columns);
		if (pconnection->result && row < pconnection->rows)
		{
			/* check column */
			if (column < pconnection->columns)
			{
				index = pconnection->columns * (row + 1) + column;
				dbglogex("Index of the result %d", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, index);

				*presult = pconnection->result[index];
			} /* check column success */
			else
			{
				dbglog("Column index number is too big", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
				ret = UE_DB_ERR_UNKNOWN_COLUMN;
			} /* check column failed */
		} /* check last query result success */
		else
		{
			dbglog("No last query result or row index number is too big", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			ret = UE_DB_ERR_NO_MORE_RESULT;
		} /* check last query result failed */
	} /* parameter check failed */
	else
	{
		dbglog("Input parameter is invalid", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		ret = UE_DB_ERR_INVALID_PARAMETER;
	} /* parameter check failed */

	dbglog("Exit uedb_sqlite3_result", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return ret;
}